package com.elite;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;



import oracle.jdbc.OracleTypes;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;



public class FtpBean {
	private static final Log log=LogFactory.getLog(FtpBean.class);
    private DataSource dataSource;
    private DataSourceTransactionManager tran;
    private PlatformTransactionManager transactionManager;
    public static String[] a;
    public static String sql123;
    

	public static String getSql123() {
		return sql123;
	}
	public static void setSql123(String sql123) {
		FtpBean.sql123 = sql123;
	}
	public static String[] getA() {
		return a;
	}
	public static void setA(String[] a) {
		FtpBean.a = a;
	}
	
	public PlatformTransactionManager getTransactionManager() {
		return transactionManager;
	}
	public void setTransactionManager(PlatformTransactionManager transactionManager) {
		this.transactionManager = transactionManager;
	}
	public DataSource getDataSource() {
		return dataSource;
	}
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public DataSourceTransactionManager getTran() {
		return tran;
	}
	public void setTran(DataSourceTransactionManager tran) {
		this.tran = tran;
	}
    public List chaxun(String sql) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List resultList = jdbcTemplate.queryForList(sql);
        return resultList;
  }
    
    public int update(String sql) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        int resultList = jdbcTemplate.update(sql);
        return resultList;
  }

    public DataSource executeTestSource() {
    	return dataSource;
    	}
    
    public void insertUser() {
    	TransactionTemplate tt =new TransactionTemplate(getTransactionManager());
    	tt.execute(new TransactionCallback() {
    	public Object doInTransaction(TransactionStatus status) {
    	JdbcTemplate jt = new JdbcTemplate(executeTestSource());
    	for(int i=0;i<a.length;i++){
    		jt.update(a[i]);
    	}
    	jt.update(sql123);
    	return null;
    	}
    	});
    }
    	
    public void insertUser1(){
    	try{
    		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    		for(int i=0;i<a.length;i++){
    			int resultList = jdbcTemplate.update(a[i]);
    			log.info(a[i]+"--"+resultList);
    		}
    		int resultList1 = jdbcTemplate.update(sql123);
    		log.info(sql123+"--"+resultList1);
    	}catch(Exception e){
    		log.info("insertUser1:"+e.toString());
    	}

    }
    
//    public static CallableStatement createCallableStatement(Connection con,String name1,String name2){
//        String storedProc = "{call "+name1+"(?,?)}";// 调用的sql
//        CallableStatement cs=null;
//        try{
//        	 cs = con.prepareCall(storedProc);
//        }catch(Exception e){
//        	log.info("cs = con.prepareCall(storedProc)错误:"+e);
//        }
//        try{
//        	cs.setString(1, "'"+name2+"'");// 设置输入参数的值
//        }catch(Exception e){
//        	log.info("设置输入参数的值错误:"+e);
//        }
//        try{
//        	cs.registerOutParameter(2, OracleTypes.INTEGER);// 注册输出参数的类型
//        }catch(Exception e){
//        	log.info("注册输出参数的类型错误:"+e);
//        }
//      
//        return cs;
//    }
//    
//    public static Object doInCallableStatement(CallableStatement cs) {
//        try{
//        	cs.execute();
//        }catch(Exception e){
//        	log.info("cs.execute()错误:"+e);
//        }
//        try{
//        	   return cs.getString(2);// 获取输出参数的值
//        }catch(Exception e){
//        	log.info("cs.getString(2)"+e);
//        	return null;
//        }
//}
//
//    public void test1(String name1,String name2){
//    	Connection con1 = DataSourceUtils.getConnection(getDataSource());  
//    	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
//    	CallableStatement a=null;
//    	try{
//    		a=createCallableStatement(con1,name1,name2);
//    		log.info("a:"+a);
//    	}catch(Exception e){
//    		log.info("a错误:"+e);
//    	}
//    	Object b=null;
//    	try{
//    		b=doInCallableStatement(a);
//    		log.info("b:"+(String)b);
//    	}catch(Exception e){
//    		log.info("b错误:"+e);
//    	}
//    	String param2Value ="";
//    	try{
//    		param2Value =(String)jdbcTemplate.execute((CallableStatementCreator)a, (CallableStatementCallback)b);
//        	
//    	}catch(Exception e){
//    		log.info("param2Value错误:"+e);
//    	}
//    	log.info("param2Value:"+param2Value);
//    }
    

    
    
    
    
    
    
    public String test(String name1,String name2) {
    	log.info("---------test------------");
    	final String a=name1;
    	final String b=name2;
    	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String param2Value = (String) jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){
                    String storedProc = "{call "+a+"(?,?)}";// 调用的sql
                    CallableStatement cs = null;
                    try{
                   	 cs = con.prepareCall(storedProc);
                   }catch(Exception e){
                   	log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                   	cs.setString(1, b);// 设置输入参数的值
                   }catch(Exception e){
                   	log.info("设置输入参数的值错误:"+e);
                   }
                   try{
                   	cs.registerOutParameter(2, OracleTypes.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){
                   	log.info("注册输出参数的类型错误:"+e);
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+e);
                    }
                    try{
                    	   return cs.getString(2);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(2)"+e);
                    	return null;
                    }
            }
        });
        log.info("param2Value:"+param2Value);
        return param2Value;
    }    
    
    
    
    public String test1(String name1,String name2,String name3) {
    	log.info("---------test1------------");
    	final String a=name1;
    	final String b=name2;
    	final String c=name3;
    	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String param2Value = (String) jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){
                    String storedProc = "{call "+a+"(?,?,?)}";// 调用的sql
                    CallableStatement cs = null;
                    try{
                   	 cs = con.prepareCall(storedProc);
                   }catch(Exception e){
                   	log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                   	cs.setString(1, b);// 设置输入参数的值
                   	cs.setString(2, c);// 设置输入参数的值
                   }catch(Exception e){
                   	log.info("设置输入参数的值错误:"+e);
                   }
                   try{
                   	cs.registerOutParameter(3, OracleTypes.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){
                   	log.info("注册输出参数的类型错误:"+e);
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+e);
                    }
                    try{
                    	   return cs.getString(3);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(3)"+e);
                    	return null;
                    }
            }
        });
        log.info("param2Value:"+param2Value);
        return param2Value;
    }     
    
    
    
    
    
    

}
